import pymysql
from pymysql import Error


# 批量刷sys_role_permission表的id
# id有冲突，暂时不刷
def sync_role_permissions():
    # 数据库连接配置
    source_db_config = {
        'host': '10.1.213.159',
        'user': 'GTXS_user',
        'password': 'GTXS_user_JG123',
        'database': 'sysmandb',
        'charset': 'utf8mb4'
    }

    target_db_config = {
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'jg_usercenter',
        'password': 'Jg_usercenter_20250312',
        'database': 'jg_usercenter',
        'charset': 'utf8mb4'
    }

    try:
        # 连接源数据库和目标数据库
        source_conn = pymysql.connect(**source_db_config)
        target_conn = pymysql.connect(**target_db_config)

        with source_conn.cursor() as source_cursor, target_conn.cursor() as target_cursor:
            # 从源数据库获取所有角色权限数据
            source_cursor.execute("SELECT guid, sys_role_id as role_id, sys_permission_id as permission_id, "
                                  "create_user, create_date, change_user, change_date FROM sys_role_permission "
                                  "ORDER BY sys_role_id limit 1")
            source_data = source_cursor.fetchall()

            # 从目标数据库获取现有角色权限数据
            target_cursor.execute("SELECT role_id, permission_id, id FROM sys_role_permission")
            target_data = target_cursor.fetchall()

            # 创建目标数据的字典，方便查找
            target_dict = {(row[0], row[1]): row[2] for row in target_data}

            # 遍历源数据
            for source_row in source_data:
                # 获取目标库中的ID
                # source_row = source_dict[(role_id, permission_id)]
                source_id = source_row[0]
                role_id = source_row[1]
                permission_id = source_row[2]
                # 检查是否已存在于目标库
                if (role_id, permission_id) in target_dict:
                    # 更新记录id
                    update_query = """
                    UPDATE sys_role_permission 
                    SET id = %s 
                    WHERE role_id = %s AND permission_id = %s
                    """
                    target_cursor.execute(update_query, (source_id, role_id, permission_id))
                else:
                    # 插入新记录
                    insert_query = """
                    INSERT INTO sys_role_permission (id, role_id, permission_id, tenant_id, remark, 
                        create_user, create_date, change_user, change_date) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    target_cursor.execute(insert_query, (source_id, role_id, permission_id, '010', '从一码通主数据迁移',
                                                         source_row[3], source_row[4], source_row[5], source_row[6]))

            # 提交事务
            target_conn.commit()

            print("角色权限数据同步完成")

    except Error as e:
        print(f"数据库操作出错: {e}")
        # 回滚事务
        if 'target_conn' in locals():
            target_conn.rollback()
    finally:
        # 关闭数据库连接
        if 'source_conn' in locals():
            source_conn.close()
        if 'target_conn' in locals():
            target_conn.close()


if __name__ == "__main__":
    sync_role_permissions()
